import copy
import json
import time
import re


def init_tree(tree):
    result = re.sub(r"(\d+\|)", "", tree)
    return json.loads(eval(result))['value']


def walk_tree(tree):
    result = []

    def backtrack(tree, track):
        track = copy.deepcopy(track)
        if not tree.get('subTree', None):
            result.append(track)
            return

        if len(track)*2 == len(tree['subTree']):
            result.append(track)
            return

        for node in tree['subTree']:
            if node['treeId'] in track:
                continue

            track.append(node['treeId'])
            track.append(node['treeName'])
            backtrack(node, track)
            track.pop()
            track.pop()
    backtrack(tree, [tree['treeId'], tree['treeName']])
    return result


def sycm_cate(filename, cursor, server):
    fields = ['cate_lvl1_id', 'cate_lvl1_name', 'cate_lvl2_id', 'cate_lvl2_name',
              'cate_lvl3_id', 'cate_lvl3_name', 'sta_date']
    with open(filename, "r", encoding="utf-8", errors='ignore') as f:
        cate_name = filename.split("_")[1].strip()
        cate_tree = init_tree(f.read())
        insert = False
        for tree in cate_tree:
            args = []
            SQL = f"INSERT INTO taobao_market_cate ({','.join(fields)}) values (%s, %s, %s, %s, %s, %s, %s)"
            if tree.get("parentTreeId", None) == 0:
                insert = True
                result = walk_tree(tree)
                for item in result:
                    # 没有三级类目的情况
                    if len(item) == 4:
                        item += [None, None]
                    elif len(item) == 8:
                        item.pop()
                        item.pop()
                    item.append(time.strftime("%Y-%m-%d"))
                    args.append(item)
            cursor.executemany(SQL, args)
        server.commit()
        if not insert:
            raise Exception("only valid for cate_lvl1")


if __name__ == "__main__":
    import pymysql
    mysql_conf = {
        'host': '127.0.0.1',
        'port': 3306,
        'password': 'qwe369',
        'user': 'root',
        'database': 'tunan_class'
    }

    mysql_server = pymysql.connect(**mysql_conf)
    cursor = mysql_server.cursor()
    file_name = "婴童用品_cate.json"
    # file_name = "医疗器械_cate.json"
    sycm_cate(file_name, cursor, mysql_server)